﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// DBHelper 的摘要说明
/// </summary>
namespace LogisticsDAL
{
    public class DBHelper
    {
        public static string constr = ConfigurationManager.ConnectionStrings["str"].ToString();

        ///查询方法
        public static SqlDataReader Exreader(string sql, SqlParameter[] pram)
        {
            SqlConnection con = new SqlConnection(constr);

            con.Open();
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (pram != null)
                {
                    cmd.Parameters.AddRange(pram);
                }

                SqlDataReader Read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return Read;
            }

        }

        public static SqlDataReader getExreaders(string sql, SqlParameter[] pram)
        {
            SqlConnection con = new SqlConnection(constr);

            con.Open();
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
               
                if (pram != null)
                {
                    cmd.Parameters.AddRange(pram);
                }

                SqlDataReader Read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return Read;
            }

        }



        //受影响行数
        public static int Number(string sql, SqlParameter[] pram)
        {
            SqlConnection con = new SqlConnection(constr);
            //判断是否连接
            if (con.State == ConnectionState.Closed)
            {
                //打开连接
                con.Open();
            }
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (pram != null)
                {
                    cmd.Parameters.AddRange(pram);
                }
                int num = cmd.ExecuteNonQuery();
                con.Close();
                return num;
            }
        }



        public static int Numbers(string sql, SqlParameter[] pram)
        {
            SqlConnection con = new SqlConnection(constr);
            //判断是否连接
            if (con.State == ConnectionState.Closed)
            {
                //打开连接
                con.Open();
            }
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                //cmd.CommandType = CommandType.StoredProcedure;
                if (pram != null)
                {
                    cmd.Parameters.AddRange(pram);
                }
                int num = cmd.ExecuteNonQuery();
                con.Close();
                return num;
            }
        }


        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetTable(string sqlstr, SqlParameter[] param)
        {

            //连接到数据库
            SqlConnection conn = new SqlConnection(constr);
            //执行SQL语句
            //判断是否连接
            if (conn.State == ConnectionState.Closed)
            {
                //打开连接
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            //声明执行的类型是存储过程
            // cmd.CommandType = CommandType.StoredProcedure;
            //判断参数是否为null
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            //适配器
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //定义一个DataTable类型的对象
            DataTable dt = new DataTable();
            //将适配器中的结果填充到dt中
            sda.Fill(dt);
            //返回dt的值
            return dt;



        }



        public static DataTable GetTables(string sqlstr, SqlParameter[] param)
        {

            //连接到数据库
            SqlConnection conn = new SqlConnection(constr);
            //执行SQL语句
            //判断是否连接
            if (conn.State == ConnectionState.Closed)
            {
                //打开连接
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            //声明执行的类型是存储过程
            cmd.CommandType = CommandType.StoredProcedure;
            //判断参数是否为null
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            //适配器
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //定义一个DataTable类型的对象
            DataTable dt = new DataTable();
            //将适配器中的结果填充到dt中
            sda.Fill(dt);
            //返回dt的值
            return dt;



        }








        /// <summary>
        /// 执行SQL语句，返回首行首列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回的首行首列</returns>
        public static object GetObject(string strSQL, SqlParameter[] paras)
        {
            
            object fast = null;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
               // cmd.CommandType = cmdtype;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);

                }

                conn.Open();
                fast = cmd.ExecuteScalar();
                conn.Close();
            }
            return fast;

        }

    }
}